﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using TecData.Interfaces;
using TecData.Properties;

namespace TecData.Layer {
    public class Db<T> : IDb where T : IDbConnection, new() {
        string connectionString;

        public Db(string connectionString) {
            this.connectionString = connectionString;
        }



        IEnumerable<S> Do<R, S>(string query, Action<IDbCommand> parameterizer,
                                Func<IDbCommand, IEnumerable<R>> actor, Func<R, S> selector) {
            using (var conn = new T()) {
                using (var cmd = conn.CreateCommand()) {
                    if (parameterizer != null)
                        parameterizer(cmd);
                    cmd.CommandText = query;
                    cmd.Connection.ConnectionString = connectionString;
                    cmd.Connection.Open();
                    foreach (var item in actor(cmd))
                        yield return selector(item);
                }
            }
        }



        public IEnumerable<S> Get<S>(string query, Action<IDbCommand> parameterizer,
                                     Func<IDataRecord, S> selector) {
            return Do(query, parameterizer, ExecuteReader, selector);
        }

        static IEnumerable<IDataRecord> ExecuteReader(IDbCommand cmd) {
            using (var r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                while (r.Read())
                    yield return r;
        }

        public int Add(string query, Action<IDbCommand> parameterizer) {
            return Do(query, parameterizer, ExecuteReader, r => Convert.ToInt32(r[0])).First();
        }

        public int Save(string query, Action<IDbCommand> parameterizer) {
            return Do(query, parameterizer, ExecuteNonQuery, noAffected => noAffected).First();
        }

        static IEnumerable<int> ExecuteNonQuery(IDbCommand cmd) {
            yield return cmd.ExecuteNonQuery();
        }

        public int SaveSafely(string query, Action<IDbCommand> parameterizer) {
            // 'using' clause ensures rollback is called, so no need to explicitly rollback
            return Do(query, parameterizer, cmd => {
                using (cmd.Transaction = cmd.Connection.BeginTransaction()) {
                    var noAffected = ExecuteNonQuery(cmd);
                    cmd.Transaction.Commit();
                    return noAffected;
                }
            }, noAffected => noAffected).First();
        }
    }

    public static class Db {
        static IDb db = GetDbInstance();

        static IDb GetDbInstance() {
            // get these two from config file or somewhere
            var connectionString = Settings.Default.ConnectionString;
            var driver = Settings.Default.DbType;

            // some sort of estimation of your db
            if (driver == "SQL")
                return new Db<SqlConnection>(connectionString);
            //else if (driver == MySQL)
            //  return new Db<MySqlConnection>(connectionString);
            else if (driver == "Jet")
                return new Db<OleDbConnection>(connectionString);
            //etc

            return null;
        }

        public static void Parameterize(this IDbCommand command, string name,
                                        object value) {
            var parameter = command.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            command.Parameters.Add(parameter);
        }



        public static IEnumerable<T> Get<T>(string query,
                                            Action<IDbCommand> parameterizer,
                                            Func<IDataRecord, T> selector) {
            return db.Get(query, parameterizer, selector);
        }

        public static int Add(string query, Action<IDbCommand> parameterizer) {
            return db.Add(query, parameterizer);
        }

        public static int Save(string query, Action<IDbCommand> parameterizer) {
            return db.Save(query, parameterizer);
        }

        public static int SaveSafely(string query, Action<IDbCommand> parameterizer) {
            return db.SaveSafely(query, parameterizer);
        }
    }

}
